﻿Imports System.Data.SqlClient

Public Class Conexion
#Region "propiedades"
    Public ReadOnly Property SqlCon() As SqlConnection
        Get
            Return con
        End Get
    End Property

    Public ReadOnly Property isConected() As Boolean
        Get
            If Not con.State = ConnectionState.Closed Or con.State = ConnectionState.Broken Then
                Return True
            Else
                Return False
            End If
        End Get
    End Property
#End Region

    Dim con As SqlConnection

    Public Sub New()

    End Sub
    Public Function Conectar() As Boolean 'TODO: work with user auth not with windows auth
        Dim cnxString As String = Config.Default.DSN
        Try
            con = New SqlConnection(cnxString)
            con.Open()
            Return True
        Catch ex As Exception
            MsgBox("Error de conexion a la base de datos: \n " & ex.Message, MsgBoxStyle.Critical, "Error!")
            Return False
        End Try
    End Function
    Public Function Desconectar() As Boolean
        If Not con.State = ConnectionState.Closed Then
            con.Close()
            Return True
        Else
            Return False
        End If
    End Function

    Public Sub Insert(ByVal query As String)
        Conectar()
        Dim comand As New SqlCommand(query, con)
        Try
            comand.ExecuteNonQuery()
            Desconectar()
        Catch ex As Exception
            MsgBox(ex.Message)
            Desconectar()
        End Try
    End Sub

    Public Function getAsDataset(ByVal sql As String) As DataSet
        Conectar()
        Dim comand As New SqlCommand(sql, con)
        Dim adapter As New SqlDataAdapter(comand)
        Dim ds As New DataSet
        adapter.Fill(ds)
        If ds.Tables(0).Rows.Count > 0 Then
            Return ds
        Else
            Return Nothing
        End If
        Desconectar()
    End Function

    Public Function getDatarow(ByVal sql As String) As DataRow
        Dim fila As DataRow
        Dim ds As DataSet = getAsDataset(sql)
        fila = ds.Tables(0).Rows(0)
        Return fila
    End Function
End Class
